<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1">
    <title id="dv20941">LOCK</title>
    <body>
        <p id="sql_command_desc">Locks a table.</p>
        <section id="section2">
            <title>Synopsis</title>
            <codeblock id="sql_command_synopsis">LOCK [TABLE] [ONLY] name [ * ] [, ...] [IN <varname>lockmode</varname> MODE] [NOWAIT]</codeblock>
            <p>where <varname>lockmode</varname> is one of:</p>
            <codeblock>    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE 
  | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE</codeblock>
        </section>
        <section id="section3">
            <title>Description</title>
            <p><codeph>LOCK TABLE</codeph> obtains a table-level lock, waiting if necessary for any
                conflicting locks to be released. If <codeph>NOWAIT</codeph> is specified,
                    <codeph>LOCK TABLE</codeph> does not wait to acquire the desired lock: if it
                cannot be acquired immediately, the command is stopped and an error is emitted. Once
                obtained, the lock is held for the remainder of the current transaction. There is no
                    <codeph>UNLOCK TABLE</codeph> command; locks are always released at transaction
                end.</p>
            <p>When acquiring locks automatically for commands that reference tables, Greenplum
                Database always uses the least restrictive lock mode possible. <codeph>LOCK
                    TABLE</codeph> provides for cases when you might need more restrictive locking.
                For example, suppose an application runs a transaction at the <i>Read Committed</i>
                isolation level and needs to ensure that data in a table remains stable for the
                duration of the transaction. To achieve this you could obtain <codeph>SHARE</codeph>
                lock mode over the table before querying. This will prevent concurrent data changes
                and ensure subsequent reads of the table see a stable view of committed data,
                because <codeph>SHARE</codeph> lock mode conflicts with the <codeph>ROW
                    EXCLUSIVE</codeph> lock acquired by writers, and your <codeph>LOCK TABLE
                        <varname>name</varname> IN SHARE MODE</codeph> statement will wait until any
                concurrent holders of <codeph>ROW EXCLUSIVE</codeph> mode locks commit or roll back.
                Thus, once you obtain the lock, there are no uncommitted writes outstanding;
                furthermore none can begin until you release the lock.</p>
            <p>To achieve a similar effect when running a transaction at the <codeph>REPEATABLE
                    READ</codeph> or <codeph>SERIALIZABLE</codeph> isolation level, you have to
                run the <codeph>LOCK TABLE</codeph> statement before running any
                    <codeph>SELECT</codeph> or data modification statement. A <codeph>REPEATABLE
                    READ</codeph> or <codeph>SERIALIZABLE</codeph> transaction's view of data will
                be frozen when its first <codeph>SELECT</codeph> or data modification statement
                begins. A <codeph>LOCK TABLE</codeph> later in the transaction will still prevent
                concurrent writes — but it won't ensure that what the transaction reads corresponds
                to the latest committed values.</p>
            <p>If a transaction of this sort is going to change the data in the table, then it
                should use <codeph>SHARE ROW EXCLUSIVE</codeph> lock mode instead of
                    <codeph>SHARE</codeph> mode. This ensures that only one transaction of this type
                runs at a time. Without this, a deadlock is possible: two transactions might both
                acquire <codeph>SHARE</codeph> mode, and then be unable to also acquire <codeph>ROW
                    EXCLUSIVE</codeph> mode to actually perform their updates. Note that a
                transaction's own locks never conflict, so a transaction can acquire <codeph>ROW
                    EXCLUSIVE</codeph> mode when it holds <codeph>SHARE</codeph> mode — but not if
                anyone else holds <codeph>SHARE</codeph> mode. To avoid deadlocks, make sure all
                transactions acquire locks on the same objects in the same order, and if multiple
                lock modes are involved for a single object, then transactions should always acquire
                the most restrictive mode first.</p>
        </section>
        <section id="section4">
            <title>Parameters</title>
            <parml>
                <plentry>
                    <pt><varname>name</varname></pt>
                    <pd>The name (optionally schema-qualified) of an existing table to lock. If
                            <codeph>ONLY</codeph> is specified, only that table is locked. If
                            <codeph>ONLY</codeph> is not specified, the table and all its descendant
                        tables (if any) are locked. Optionally, <codeph>*</codeph>
                            can be specified after the table name to explicitly indicate that
                            descendant tables are included.</pd>
                    <pd>If multiple tables are given, tables are locked one-by-one in the order
                        specified in the <codeph>LOCK TABLE</codeph> command.</pd>
                </plentry>
                <plentry>
                    <pt><varname>lockmode</varname></pt>
                    <pd>The lock mode specifies which locks this lock conflicts with. If no lock
                        mode is specified, then <codeph>ACCESS EXCLUSIVE</codeph>, the most
                        restrictive mode, is used. Lock modes are as follows:<ul id="ul_cnt_mll_m4">
                            <li id="dv157736">ACCESS SHARE — Conflicts with the <codeph>ACCESS
                                    EXCLUSIVE</codeph> lock mode only. The <codeph>SELECT</codeph>
                                command acquires a lock of this mode on referenced tables. In
                                general, any query that only reads a table and does not modify it
                                will acquire this lock mode.</li>
                            <li id="dv157739">ROW SHARE — Conflicts with the
                                    <codeph>EXCLUSIVE</codeph> and <codeph>ACCESS EXCLUSIVE</codeph>
                                lock modes. The <codeph>SELECT FOR SHARE</codeph> command
                                automatically acquires a lock of this mode on the target table(s)
                                (in addition to <codeph>ACCESS SHARE</codeph> locks on any other
                                tables that are referenced but not selected <codeph>FOR
                                    SHARE</codeph>). </li>
                            <li id="dv157742">ROW EXCLUSIVE — Conflicts with the
                                    <codeph>SHARE</codeph>, <codeph>SHARE ROW EXCLUSIVE</codeph>,
                                    <codeph>EXCLUSIVE</codeph>, and <codeph>ACCESS
                                    EXCLUSIVE</codeph> lock modes. The commands
                                    <codeph>INSERT</codeph> and <codeph>COPY</codeph> automatically
                                acquire this lock mode on the target table (in addition to
                                    <codeph>ACCESS SHARE</codeph> locks on any other referenced
                                tables) See <xref href="#topic1/lock_note" format="dita"
                                >Note</xref>. </li>
                            <li id="dv157745">SHARE UPDATE EXCLUSIVE — Conflicts with the
                                    <codeph>SHARE UPDATE EXCLUSIVE</codeph>, <codeph>SHARE</codeph>,
                                    <codeph>SHARE ROW EXCLUSIVE</codeph>,
                                <codeph>EXCLUSIVE</codeph>, and <codeph>ACCESS EXCLUSIVE</codeph>
                                lock modes. This mode protects a table against concurrent schema
                                changes and <codeph>VACUUM</codeph> runs. Acquired by
                                    <codeph>VACUUM</codeph> (without <codeph>FULL</codeph>) on heap
                                tables and <codeph>ANALYZE</codeph>.</li>
                            <li id="dv157748">SHARE — Conflicts with the <codeph>ROW
                                    EXCLUSIVE</codeph>, <codeph>SHARE UPDATE EXCLUSIVE</codeph>,
                                    <codeph>SHARE ROW EXCLUSIVE, EXCLUSIVE</codeph>, and
                                    <codeph>ACCESS EXCLUSIVE</codeph> lock modes. This mode protects
                                a table against concurrent data changes. Acquired automatically by
                                    <codeph>CREATE INDEX</codeph>.</li>
                            <li id="dv157751">SHARE ROW EXCLUSIVE — Conflicts with the <codeph>ROW
                                    EXCLUSIVE</codeph>, <codeph>SHARE UPDATE EXCLUSIVE</codeph>,
                                    <codeph>SHARE</codeph>, <codeph>SHARE ROW EXCLUSIVE</codeph>,
                                    <codeph>EXCLUSIVE</codeph>, and <codeph>ACCESS
                                    EXCLUSIVE</codeph> lock modes. This lock mode is not
                                automatically acquired by any Greenplum Database command. </li>
                            <li id="dv157754">EXCLUSIVE — Conflicts with the <codeph>ROW
                                    SHARE</codeph>, <codeph>ROW EXCLUSIVE</codeph>, <codeph>SHARE
                                    UPDATE EXCLUSIVE</codeph>, <codeph>SHARE</codeph>, <codeph>SHARE
                                    ROW EXCLUSIVE</codeph>, <codeph>EXCLUSIVE</codeph>, and
                                    <codeph>ACCESS EXCLUSIVE</codeph> lock modes. This mode allows
                                only concurrent <codeph>ACCESS SHARE</codeph> locks, i.e., only
                                reads from the table can proceed in parallel with a transaction
                                holding this lock mode. This lock mode is automatically acquired for
                                    <codeph>UPDATE</codeph>, <codeph>SELECT FOR UPDATE</codeph>, and
                                    <codeph>DELETE</codeph> in Greenplum Database (which is more
                                restrictive locking than in regular PostgreSQL). See <xref
                                    href="#topic1/lock_note" format="dita">Note</xref>.</li>
                            <li id="dv157757">ACCESS EXCLUSIVE — Conflicts with locks of all modes
                                    (<codeph>ACCESS SHARE</codeph>, <codeph>ROW SHARE</codeph>,
                                    <codeph>ROW EXCLUSIVE</codeph>, <codeph>SHARE UPDATE
                                    EXCLUSIVE</codeph>, <codeph>SHARE</codeph>,
                                    <codeph>SHARE</codeph><codeph>ROW EXCLUSIVE</codeph>,
                                    <codeph>EXCLUSIVE</codeph>, and <codeph>ACCESS
                                    EXCLUSIVE</codeph>). This mode guarantees that the holder is the
                                only transaction accessing the table in any way. Acquired
                                automatically by the <codeph>ALTER TABLE</codeph>, <codeph>DROP
                                    TABLE</codeph>, <codeph>TRUNCATE</codeph>,
                                    <codeph>REINDEX</codeph>, <codeph>CLUSTER</codeph>, and
                                    <codeph>VACUUM FULL</codeph> commands. This is the default lock
                                mode for <codeph>LOCK TABLE</codeph> statements that do not specify
                                a mode explicitly. This lock is also briefly acquired by
                                    <codeph>VACUUM</codeph> (without <codeph>FULL</codeph>) on
                                append-optimized tables during processing. </li>
                        </ul>
                        <note>By default Greenplum Database acquires the more restrictive
                                <codeph>EXCLUSIVE</codeph> lock (rather than <codeph>ROW
                                EXCLUSIVE</codeph> in PostgreSQL) for <codeph>UPDATE</codeph>,
                                <codeph>DELETE</codeph>, and <codeph>SELECT...FOR UPDATE</codeph>
                            operations on heap tables. When the Global Deadlock Detector is enabled
                            the lock mode for <codeph>UPDATE</codeph> and <codeph>DELETE</codeph>
                            operations on heap tables is <codeph>ROW EXCLUSIVE</codeph>. See <xref
                                href="../../admin_guide/dml.html#topic_gdd" format="html" scope="external">Global Deadlock
                                Detector</xref>. Greenplum always holds a table-level lock with
                                <codeph>SELECT...FOR UPDATE</codeph> statements. </note></pd>
                </plentry>
                <plentry>
                    <pt>NOWAIT</pt>
                    <pd>Specifies that <codeph>LOCK TABLE</codeph> should not wait for any
                        conflicting locks to be released: if the specified lock(s) cannot be
                        acquired immediately without waiting, the transaction is cancelled.
                    </pd>
                </plentry>
            </parml>
        </section>
        <section id="section5">
            <title>Notes</title>
            <p><codeph>LOCK TABLE ... IN ACCESS SHARE MODE</codeph> requires <codeph>SELECT</codeph>
                privileges on the target table. All other forms of <codeph>LOCK</codeph> require
                table-level <codeph>UPDATE</codeph>, <codeph>DELETE</codeph>, or
                    <codeph>TRUNCATE</codeph> privileges.</p>
            <p><codeph>LOCK TABLE</codeph> is useless outside of a transaction block: the lock would
                be held only to the completion of the <codeph>LOCK</codeph> statement. Therefore,
                Greenplum Database reports an error if <codeph>LOCK</codeph> is used outside of a
                transaction block. Use <codeph>BEGIN</codeph> and <codeph>END</codeph> to define a
                transaction block. </p>
            <p><codeph>LOCK TABLE</codeph> only deals with table-level locks, and so the mode names
                involving <codeph>ROW</codeph> are all misnomers. These mode names should generally
                be read as indicating the intention of the user to acquire row-level locks within
                the locked table. Also, <codeph>ROW EXCLUSIVE</codeph> mode is a shareable table
                lock. Keep in mind that all the lock modes have identical semantics so far as
                    <codeph>LOCK TABLE</codeph> is concerned, differing only in the rules about
                which modes conflict with which. For information on how to acquire an actual
                row-level lock, see the <codeph>FOR UPDATE/FOR SHARE</codeph> clause in the
                        <codeph><xref href="./SELECT.xml#topic1" type="topic" format="dita"
                    /></codeph> reference documentation.</p>
        </section>
        <section id="section6">
            <title>Examples</title>
            <p>Obtain a <codeph>SHARE</codeph> lock on the <codeph>films</codeph> table when going
                to perform inserts into the <codeph>films_user_comments</codeph> table:</p>
            <codeblock>BEGIN WORK;
LOCK TABLE films IN SHARE MODE;
SELECT id FROM films
    WHERE name = 'Star Wars: Episode I - The Phantom Menace';
-- Do ROLLBACK if record was not returned
INSERT INTO films_user_comments VALUES
    (_id_, 'GREAT! I was waiting for it for so long!');
COMMIT WORK;</codeblock>
            <p>Take a <codeph>SHARE ROW EXCLUSIVE</codeph> lock on a table when performing a delete
                operation:</p>
            <codeblock>BEGIN WORK;
LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
DELETE FROM films_user_comments WHERE id IN
    (SELECT id FROM films WHERE rating &lt; 5);
DELETE FROM films WHERE rating &lt; 5;
COMMIT WORK;</codeblock>
        </section>
        <section id="section7">
            <title>Compatibility</title>
            <p>There is no <codeph>LOCK TABLE</codeph> in the SQL standard, which instead uses
                    <codeph>SET TRANSACTION</codeph> to specify concurrency levels on transactions.
                Greenplum Database supports that too. </p>
            <p>Except for <codeph>ACCESS SHARE</codeph>, <codeph>ACCESS EXCLUSIVE</codeph>, and
                    <codeph>SHARE UPDATE EXCLUSIVE</codeph> lock modes, the Greenplum Database lock
                modes and the <codeph>LOCK TABLE</codeph> syntax are compatible with those present
                in Oracle.</p>
        </section>
        <section id="section8">
            <title>See Also</title>
            <p><codeph><xref href="BEGIN.xml#topic1" type="topic" format="dita"/></codeph>,
                        <codeph><xref href="./SET_TRANSACTION.xml#topic1" type="topic" format="dita"
                    /></codeph>, <codeph><xref href="./SELECT.xml#topic1" type="topic" format="dita"
                    /></codeph></p>
        </section>
    </body>
</topic>
